package com.erpoa.mapper;

import com.erpoa.entity.RecMatEntity;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;

/**
 * @author chenjimin
 * @Title: GoodRetMaterialMapper
 * @ProjectName oaerp
 * @Description: TODO
 * @date 2020/9/2213:12
 */
@Mapper
public interface GoodRetMaterialMapper {
    @Select("SELECT  FID fPlanID,FORDERNO fOrderNo,FPLANQTY fPlanQty,FMACHINECODE fMachineCode,FFACTORYCODE,FREMARK fRemark,NVL(MIN(NVL(FHAVESENDQTY,0) - (CASE WHEN FNEEDSENDQTY=0 THEN 1 "
            + "ELSE FNEEDSENDQTY END)),0) AS fWeight ,fmatcode fMatCode FROM ( SELECT T1.FID,T1.FORDERNO,T1.FPLANQTY,T1.FMACHINECODE,T1.FFACTORYCODE,"
            + "T1.FREMARK,T2.FMATCODE,T2.FHAVESENDQTY,ROUND(T3.SFA161*T1.FPLANQTY,3) AS FNEEDSENDQTY  FROM MG_ORDERPLAN T1 LEFT JOIN "
            + "(SELECT FPLANID,FMATCODE,NVL(SUM(CASE WHEN FOPTYPE='领料' THEN FRSPQTY WHEN FOPTYPE='退料' THEN FRSPQTY*-1 END),0) AS "
            + "FHAVESENDQTY FROM MG_RECMAT WHERE FPRODUCTYPE=#{library} GROUP BY FPLANID,FMATCODE) T2 ON T2.FPLANID=T1.FID LEFT JOIN "
            + "(SELECT SFA01,SFA03,SFA161 FROM DS2.SFA_FILE) T3 ON T3.SFA01=T1.FORDERNO AND T3.SFA03= T2.FMATCODE JOIN DS2.TC_PGEN_FILE T4 "
            + " ON T4.TC_PGEN02=T1.FFACTORYCODE AND T4.TC_PGEN03=T1.FWORKSHOPCODE AND T4.TC_PGEN01=#{sCode} WHERE  T1.FMACHINECODE=(CASE WHEN "
            + "T1.FFACTORYCODE = 'E' THEN 'GZ'||#{machine} WHEN  FFACTORYCODE != 'E' THEN #{machine} END) AND T1.FORDERSTATUS='生产中') GROUP BY FID,FORDERNO,"
            + "FPLANQTY,FMACHINECODE,FFACTORYCODE,FREMARK,fmatcode")
    List<RecMatEntity> selectFbatchExistsByParam(@Param("library") String library, @Param("sCode") String sCode, @Param("machine") String machine);

    @Select(" SELECT NVL(MIN(NVL(A.FHAVESENDQTY,0) - (CASE WHEN B.FNEEDSENDQTY = 0 THEN 1 ELSE B.FNEEDSENDQTY END)),0) "
            + " AS fWeight  FROM ( SELECT FMATCODE,NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY WHEN  FOPTYPE = '退料'"
            + " THEN FRSPQTY*-1 END),0)  AS FHAVESENDQTY FROM MG_RECMAT WHERE FPLANID = #{fPlanID}  AND FPRODUCTYPE=#{fType} GROUP BY FMATCODE ) A  RIGHT JOIN"
            + " (SELECT SFA01,SFA03,ROUND(SFA161*(SELECT FPLANQTY FROM MG_ORDERPLAN WHERE FID=#{fPlanID}), 3) FNEEDSENDQTY FROM "
            + "DS2.SFA_FILE WHERE SFA03=(SELECT DISTINCT FMATCODE FROM MG_RECMAT WHERE FPLANID=#{fPlanID} AND FPRODUCTYPE=#{fType} ) "
            + " AND SFA01 = (SELECT FORDERNO FROM MG_ORDERPLAN WHERE FID=#{fPlanID}) ) B ON A.FMATCODE = B.SFA03")
    RecMatEntity selectFweightByParam(@Param("fPlanID") String fPlanID, @Param("fType") String fType);

    @Select(" SELECT COUNT(sfb04) FROM DS2.sfb_file WHERE sfb01=#{fOrderNo} and sfb04='8'")
    int countSfb04ByOrderNo(@Param("fOrderNo") String fOrderNo);

    @Select(" SELECT COUNT(FID) FROM MG_RECMATDETAIL WHERE FPLANID=#{fPlanID} AND FBATCH=#{fBarCode}")
    int countFidByParam(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);

    @Select(" SELECT NVL(MIN(NVL(A.FHAVESENDQTY,0) - (CASE WHEN B.FNEEDSENDQTY = 0 THEN 1 ELSE B.FNEEDSENDQTY END)),0) "
            + " AS LDIFFRESULT  FROM ( SELECT FMATCODE,NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY WHEN  FOPTYPE = '退料'"
            + " THEN FRSPQTY*-1 END),0)  AS FHAVESENDQTY FROM MG_RECMAT WHERE FPLANID = #{fPlanID}  AND FPRODUCTYPE=#{fType} GROUP BY FMATCODE ) A  RIGHT JOIN"
            + " (SELECT SFA01,SFA03,ROUND(SFA161*(SELECT FPLANQTY FROM MG_ORDERPLAN WHERE FID=#{fPlanID}), 3) FNEEDSENDQTY FROM "
            + "DS2.SFA_FILE WHERE SFA03=(SELECT DISTINCT FMATCODE FROM MG_RECMAT WHERE FPLANID=#{fPlanID} AND FPRODUCTYPE=#{fType} ) "
            + " AND SFA01 = (SELECT FORDERNO FROM MG_ORDERPLAN WHERE FID=#{fPlanID}) ) B ON A.FMATCODE = B.SFA03")
    Double selectWeightByParam(@Param("fPlanID") String fPlanID, @Param("fType") String fType);

    @Select(" SELECT DISTINCT FID FID,FPARENTID FPARENTID,FFACTORYCODE FFACTORYCODE,FWORKSHOPCODE FWORKSHOPCODE FROM MG_RECMATDETAIL WHERE FPLANID=#{fPlanID} AND FBATCH=#{fBarCode} AND ROWNUM<2")
    HashMap selectDFFWCodeByParam(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);

    @Select("SELECT NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY WHEN  FOPTYPE = '退料' THEN FRSPQTY*-1 END),0) AS Qty" +
            " FROM MG_RECMATDETAIL WHERE FPLANID=#{fPlanID} AND FBATCH=#{fBarCode}")
    double selectQtypsqByParam(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);

    @Select(" SELECT A.TC_IMAM08 TC_IMAM08,A.TC_IMAM09 TC_IMAM09 FROM DS2.TC_IMAM_FILE A,MG_RECMAT B WHERE A.TC_IMAM01=B.FMATCODE AND "
            + "A.TC_IMAM16=B.FFACTORYCODE AND A.TC_IMAM17=B.FWORKSHOPCODE AND B.FID=#{fRecID}")
    HashMap selectWareHouseByFrecId(@Param("fRecID") String fRecID);

    @Select("SELECT FID FROM (SELECT * FROM MG_BATCHMAT WHERE FBARCODE=#{fBarCode} AND FFACTORYCODE=#{fFactoryCode} AND FWORKSHOPCODE=#{fWorkShopCode} ORDER BY  FRECEIVEDATE ) WHERE ROWNUM<2 ")
    String selectFidByParam(@Param("fBarCode") String fBarCode, @Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode);

    @Select("select FMATCODE,FPROCSS from dsx5.mg_recmat where fid = #{fRecID}")
    HashMap selectmatproByFrecId(@Param("fRecID") String fRecID);

    @Select("select count(0) from ds2.sfa_file where sfa01 = #{fOrderNo} and sfa03 = #{matCode} and sfa08 = #{procss} and sfa062>0")
    int countsfaByParam(@Param("fOrderNo") String fOrderNo, @Param("matCode") String matCode, @Param("procss") String procss);

    @Insert("INSERT INTO MG_BATCHMAT(FID ,VERSION,FBARCODE ,FVENDERCODE,FTYPE, FCOLOR,FMATCODE,FMATNAME,FBATCHNO,"
            + "FPRODUCTIONDATE,FQTY ,FRECEIVEDATE,FFACTORYCODE,FWORKSHOPCODE) SELECT #{batchMatID},VERSION,#{fBarCode} ,FVENDERCODE,FTYPE, FCOLOR,FMATCODE,FMATNAME,FBATCHNO,"
            + "FPRODUCTIONDATE,#{fRspQty} ,FRECEIVEDATE,FFACTORYCODE,FWORKSHOPCODE FROM (SELECT * FROM  MG_BATCHMAT " +
            "WHERE FBARCODE=#{retBarCode} AND FFACTORYCODE=#{fFactoryCode} AND　FWORKSHOPCODE=#{fWorkShopCode} ORDER BY FRECEIVEDATE) WHERE ROWNUM<2")
    int insertBatchMatByParam(@Param("batchMatID") String batchMatID, @Param("fBarCode") String fBarCode,
                              @Param("fRspQty") Double fRspQty, @Param("retBarCode") String retBarCode, @Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode);

    @Insert("insert into mg_batchmatrecord"
            + "  (fid, parantid, fvendercode, ftype, fbarcode, fcolor, fmatcode, fmatname, fbatchno, frecordtime, fqty, ffactorycode, " +
            "fworkshopcode,FUNCTIONNAME,USERNAME,TYPE,STATUS)"
            + " SELECT #{uuid},#{batchMatID} ,FVENDERCODE,FTYPE,#{fBarCode},FCOLOR,FMATCODE,FMATNAME,FBATCHNO, "
            + " sysdate,#{fRspQty} ,FFACTORYCODE,FWORKSHOPCODE,'批次退料',#{sCode},#{fType},'1' FROM (SELECT * FROM " +
            " MG_BATCHMAT WHERE FBARCODE=#{fBarCode} AND FFACTORYCODE=#{fFactoryCode} AND　FWORKSHOPCODE=#{fWorkShopCode} ORDER BY FRECEIVEDATE) WHERE ROWNUM<2")
    int insertMgBatchrecodeByParam(@Param("uuid") String uuid, @Param("batchMatID") String batchMatID, @Param("fBarCode") String fBarCode,
                                   @Param("fRspQty") Double fRspQty,
                                   @Param("sCode") String sCode, @Param("fType") String fType,
                                   @Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode);

    @Update("UPDATE MG_BATCHMAT SET FQTY=NVL(FQTY,0)+#{fRspQty} WHERE FID=#{batchID} ")
    int updateBatchMatByParam(@Param("fRspQty") Double fRspQty, @Param("batchID") String batchID);

    @Insert("insert into mg_batchmatrecord"
            + "  (fid, parantid, fvendercode, ftype, fbarcode, fcolor, fmatcode, fmatname, fbatchno, frecordtime, fqty, ffactorycode, fworkshopcode,FUNCTIONNAME,USERNAME,TYPE,STATUS)"
            + " select #{uuid},fid,fvendercode,ftype,fbarcode,fcolor,fmatcode, fmatname,fbatchno,sysdate,#{fRspQty},ffactorycode,fworkshopcode," +
            "'批次退料',#{sCode},#{fType},'2' from mg_batchmat "
            + " where fid = #{batchID}")
    int insertBatchMatByParams(@Param("uuid") String uuid, @Param("fRspQty") Double fRspQty, @Param("sCode") String sCode,
                               @Param("fType") String fType, @Param("batchID") String batchID);

    @Insert("INSERT INTO MG_RECMAT(FID,VERSION ,FPLANID,FMACHINECODE,FORDERNO, FMATCODE,FMATNAME,FOPTYPE,"
            + "FRQSPER,FRQSTIME,FRQSQTY,FRSPQTY,FRSPPER,FRSPTIME ,FWARECODE,FWARENAME,FSETS , FREASON,FSEATCODE,FSEATNAME,FUNIT,"
            + "FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME, FPROCSS,FDEALSTATUS,FVENDERNAME,FPRODUCTYPE )SELECT #{recMatID},0 ,"
            + "FPLANID,FMACHINECODE,FORDERNO, FMATCODE,FMATNAME,#{opType},#{sCode},sysdate,#{originalQty},#{originalQty},#{sCode},sysdate ,#{fWareCode},"
            + "FWARENAME,FSETS ,FREASON,#{fSeatCode},FSEATNAME,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME, FPROCSS,"
            + "FDEALSTATUS,FVENDERNAME,FPRODUCTYPE FROM MG_RECMAT WHERE FID=#{fRecID} ")
    int insertRecmatByParam(@Param("recMatID") String recMatID, @Param("opType") String opType, @Param("sCode") String sCode,
                            @Param("originalQty") double originalQty, @Param("fWareCode") String fWareCode,
                            @Param("fSeatCode") String fSeatCode, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO MG_RECMATDETAIL (FID ,VERSION,FPLANID ,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,FOPTYPE,FRQSPER,"
            + "FRQSTIME,FRQSQTY ,FRSPQTY ,FRSPPER,FRSPTIME,FWARECODE,FWARENAME,FSETS,FREASON,FSEATCODE,FSEATNAME,FUNIT,FFACTORYCODE,"
            + "FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,FPROCSS,FPARENTID,FISHANDOVER,FCODE ,FSEQCODE,FBATCH,FREALRECQTY) SELECT #{uuId} ,0,FPLANID ,FMACHINECODE,"
            + "FORDERNO,FMATCODE,FMATNAME,'退料',#{sCode},sysdate,#{originalQty} ,#{originalQty} ,#{sCode},sysdate,#{fWareCode}," +
            "FWARENAME,FSETS,'退料',#{fSeatCode},FSEATNAME,"
            + "FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,FPROCSS,#{recMatID},'Y',FCODE ,FSEQCODE,#{fBarCode},#{fRspQty} " +
            "FROM MG_RECMATDETAIL  WHERE FID=#{detailID}")
    int insertDetailByParam(@Param("uuId") String uuId, @Param("sCode") String sCode, @Param("originalQty") double originalQty,
                            @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("recMatID") String recMatID,
                            @Param("fBarCode") String fBarCode, @Param("fRspQty") Double fRspQty, @Param("detailID") String detailID);

    @Insert(" INSERT INTO MG_PROCESSBARCODE (FID,VERSION,FPLANID,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,FTYPE,FQTY,"
            + " FCREATEPER,FCREATEDATE,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,FPLANQTY,FBATCH,FVENDERCODE,FKIND,FCOLOR,FBARCODE,FBARCODETYPE)"
            + " SELECT #{uuId},0,FID,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,#{fModelNo},#{originalQty} ,#{sCode}," +
            " sysdate,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,"
            + " FPLANQTY,#{fBatch},#{fVenderCode},#{fType},#{fColor},#{fBarCode},2 FROM MG_ORDERPLAN WHERE FID=#{fPlanID} ")
    int insertBarCodeByParam(@Param("uuId") String uuId, @Param("fModelNo") String fModelNo, @Param("originalQty") double originalQty,
                             @Param("sCode") String sCode, @Param("fBatch") String fBatch, @Param("fVenderCode") String fVenderCode,
                             @Param("fType") String fType, @Param("fColor") String fColor, @Param("fBarCode") String fBarCode,
                             @Param("fPlanID") String fPlanID);

    @Insert("INSERT INTO TC_X5_LOGFILE(TC_X501,TC_X502,TC_X503,TC_X504,TC_X505,TC_X506,TC_X507,TC_X509,TC_X510,TC_X511,"
            + "TC_X513,TC_X514,TC_X515,TC_X516,TC_X5ID) SELECT FFACTORYCODE,FWORKSHOPCODE,FORDERNO,FMATCODE,2,#{fWareCode},#{fSeatCode}," +
            "#{adjustQty},sysdate,#{sCode},"
            + "#{fBarCode},FMACHINECODE,'退料',FPROCSS ,#{uuId} FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertX5LOGByParam(@Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("adjustQty") double adjustQty,
                           @Param("sCode") String sCode, @Param("fBarCode") String fBarCode, @Param("uuId") String uuId, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO TC_X5_FILE(TC_X501,TC_X502,TC_X503,TC_X504,TC_X505,TC_X506,TC_X507,TC_X509,TC_X510,TC_X511,"
            + "TC_X513,TC_X514,TC_X515,TC_X516,TC_X5ID) SELECT FFACTORYCODE,FWORKSHOPCODE,FORDERNO,FMATCODE,2,#{fWareCode},#{fSeatCode}," +
            "#{adjustQty},sysdate,#{sCode},"
            + "#{fBarCode},FMACHINECODE,'退料',FPROCSS ,#{uuId} FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertX5ByParam(@Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("adjustQty") double adjustQty,
                        @Param("sCode") String sCode, @Param("fBarCode") String fBarCode, @Param("uuId") String uuId, @Param("fRecID") String fRecID);
}
